﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class CalendarDao
    {
        public bool Delete(SqlConnection conn, int calId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DELETE FROM [dbo].[Calendar] ");
            builder.Append("WHERE ");
            builder.Append("\t[CalId] = @CalId ");
            builder.Append("\tAND\t ( ");
            builder.Append("\t\t\t([UserId] = @UserId) ");
            builder.Append("\t\t\tOR(ManagerId = @UserId) ");
            builder.Append("\t\t) ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@CalId", SqlDbType.Int);
            commandParameters[0].Value = calId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, CalendarData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT [dbo].[Calendar] ");
            builder.Append("( ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[StartTime], ");
            builder.Append("\t[EndTime], ");
            builder.Append("\t[CalType], ");
            builder.Append("\t[Content], ");
            builder.Append("\t[ManagerId] ");
            builder.Append(") ");
            builder.Append("VALUES ");
            builder.Append("( ");
            builder.Append("\t@UserId, ");
            builder.Append("\t@StartTime, ");
            builder.Append("\t@EndTime, ");
            builder.Append("\t@CalType, ");
            builder.Append("\t@Content, ");
            builder.Append("\t@ManagerId ");
            builder.Append(") ");
            builder.Append("SELECT @@IDENTITY AS RetValue ");
            SqlParameter[] commandParameters = new SqlParameter[6];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = data.UserId;
            commandParameters[1] = new SqlParameter("@StartTime", SqlDbType.DateTime);
            commandParameters[1].Value = data.StartTime;
            commandParameters[2] = new SqlParameter("@EndTime", SqlDbType.DateTime);
            commandParameters[2].Value = data.EndTime;
            commandParameters[3] = new SqlParameter("@CalType", SqlDbType.Int);
            commandParameters[3].Value = data.CalType;
            commandParameters[4] = new SqlParameter("@Content", SqlDbType.NText);
            commandParameters[4].Value = data.Content;
            commandParameters[5] = new SqlParameter("@ManagerId", SqlDbType.NVarChar, 20);
            if (data.ManagerIdIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.ManagerId;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, builder.ToString(), commandParameters);
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectByCalId(SqlConnection conn, int calId, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[CalId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[StartTime], ");
            builder.Append("\t[EndTime], ");
            builder.Append("\t[CalType], ");
            builder.Append("\t[Content], ");
            builder.Append("\t[ManagerId], ");
            builder.Append("\tdbo.GetUserName([ManagerId]) AS ManagerUserName ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Calendar] ");
            builder.Append("WHERE ");
            builder.Append("\t[CalId] = @CalId ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@CalId", SqlDbType.Int);
            commandParameters[0].Value = calId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Calendar";
            return set.Tables[0];
        }

        public DataTable SpecifyDay(SqlConnection conn, string userId, DateTime specifyDate)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[CalId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[StartTime], ");
            builder.Append("\t[EndTime], ");
            builder.Append("\t[CalType], ");
            builder.Append("\t[Content], ");
            builder.Append("             [ManagerId], ");
            builder.Append("             dbo.GetUserName([ManagerId]) AS ManagerUserName ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Calendar] ");
            builder.Append("WHERE ");
            builder.Append("\tUserId=@UserId ");
            builder.Append("\tAND Datediff(day,[StartTime],@SpecifyDate)=0  ");
            builder.Append("ORDER BY ");
            builder.Append("\t[StartTime] ASC ");
            SqlParameter[] commandParameters = new SqlParameter[2];
            commandParameters[0] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[0].Value = userId;
            commandParameters[1] = new SqlParameter("@SpecifyDate", SqlDbType.DateTime);
            commandParameters[1].Value = specifyDate;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Calendar";
            return set.Tables[0];
        }

        public DataTable Today(SqlConnection conn, string userId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[CalId], ");
            builder.Append("\t[UserId], ");
            builder.Append("\t[StartTime], ");
            builder.Append("\t[EndTime], ");
            builder.Append("\t[CalType], ");
            builder.Append("\t[Content] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Calendar] ");
            builder.Append("WHERE ");
            builder.Append("\t[UserId] = @UserId  ");
            builder.Append("\tAND Datediff(day,[StartTime],GETDATE())=0  ");
            builder.Append("ORDER BY ");
            builder.Append("\t[StartTime] ASC ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@UserId", SqlDbType.NVarChar, 20) };
            commandParameters[0].Value = userId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Calendar";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, CalendarData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE ");
            builder.Append("\t[dbo].[Calendar] ");
            builder.Append("SET ");
            builder.Append("\t[StartTime] = @StartTime, ");
            builder.Append("\t[EndTime] = @EndTime,  ");
            builder.Append("\t[CalType] = @CalType, ");
            builder.Append("\t[Content] = @Content , ");
            builder.Append("\t[ManagerId] = @ManagerId ");
            builder.Append("WHERE ");
            builder.Append("\t[CalId] = @CalId ");
            builder.Append("\tAND [UserId] = @UserId ");
            SqlParameter[] commandParameters = new SqlParameter[7];
            commandParameters[0] = new SqlParameter("@CalId", SqlDbType.Int);
            commandParameters[0].Value = data.CalId;
            commandParameters[1] = new SqlParameter("@UserId", SqlDbType.NVarChar, 20);
            commandParameters[1].Value = data.UserId;
            commandParameters[2] = new SqlParameter("@StartTime", SqlDbType.DateTime);
            commandParameters[2].Value = data.StartTime;
            commandParameters[3] = new SqlParameter("@EndTime", SqlDbType.DateTime);
            commandParameters[3].Value = data.EndTime;
            commandParameters[4] = new SqlParameter("@CalType", SqlDbType.Int);
            commandParameters[4].Value = data.CalType;
            commandParameters[5] = new SqlParameter("@Content", SqlDbType.NText);
            commandParameters[5].Value = data.Content;
            commandParameters[6] = new SqlParameter("@ManagerId", SqlDbType.NVarChar, 20);
            if (data.ManagerIdIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.ManagerId;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

